﻿using System;
using System.Collections.Generic;
using System.Windows.Forms;

//SQLite references to use for the database
using System.Data;
using System.Data.SQLite;

namespace iPower
{
    /* http://www.dreamincode.net/forums/topic/157830-using-sqlite-with-c%23/ */
    /* http://www.w3resource.com/sql/joins/using-a-where-cluase-to-join-two-tables-related-by-a-single-column-primary-key-or-foriegn-key-pair.php */
    /* http://www.sqlite.org/foreignkeys.html */

    /* -- delete from sqlite_sequence where name='your_table'; */

    /* delete from sites; */

    class SQLiteDatabase
    {
        String dbConnection;        

        public SQLiteDatabase()
	    {
            dbConnection = "Data Source=iPowerDB.s3db";
	    } //SQLiteDatabase

        public SQLiteDatabase(String inputFile)
	    {
            dbConnection = String.Format("Data Source={0}", inputFile);
	    } //SQLiteDatabase

        public SQLiteDatabase(Dictionary<String, String> connectionOpts)
	    {
	        String str = "";
	        foreach (KeyValuePair<String, String> row in connectionOpts)
	        {
	            str += String.Format("{0}={1}; ", row.Key, row.Value);
	        }
	        str = str.Trim().Substring(0, str.Length - 1);
	        dbConnection = str;
	    } //SQLiteDatabase

        public DataTable GetDataTable(string sql)
	    {
	        DataTable dt = new DataTable();
	        try
	        {
	            SQLiteConnection cnn = new SQLiteConnection(dbConnection);
	            cnn.Open();
	            SQLiteCommand mycommand = new SQLiteCommand(cnn);
	            mycommand.CommandText = sql;
	            SQLiteDataReader reader = mycommand.ExecuteReader();
	            dt.Load(reader);
	            reader.Close();
	            cnn.Close();
	        }
	        catch (Exception e)
	        {
	            throw new Exception(e.Message);
	        }
	        return dt;
	    } //GetDataTable

        public int ExecuteNonQuery(string sql)
	    {
	        SQLiteConnection cnn = new SQLiteConnection(dbConnection);
	        cnn.Open();
	        SQLiteCommand mycommand = new SQLiteCommand(cnn);
	        mycommand.CommandText = sql;
	        int rowsUpdated = mycommand.ExecuteNonQuery();
	        cnn.Close();
	        return rowsUpdated;
	    } //ExecuteNonQuery

        public string ExecuteScalar(string sql)
	    {
	        SQLiteConnection cnn = new SQLiteConnection(dbConnection);
	        cnn.Open();
	        SQLiteCommand mycommand = new SQLiteCommand(cnn);
	        mycommand.CommandText = sql;
	        object value = mycommand.ExecuteScalar();
	        cnn.Close();
	        if (value != null)
	        {
	            return value.ToString();
	        }
	        return "";
	    } //ExecuteScalar

        public bool Update(String tableName, Dictionary<String, String> data, String where)
	    {
	        String vals = "";
	        Boolean returnCode = true;
	        if (data.Count >= 1)
	        {
	            foreach (KeyValuePair<String, String> val in data)
	            {
	                vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString());
	            }
	            vals = vals.Substring(0, vals.Length - 1);
	        }
	        try
	        {
	            this.ExecuteNonQuery(String.Format("update {0} set {1} where {2};", tableName, vals, where));
	        }
	        catch
	        {
	            returnCode = false;
	        }
	        return returnCode;
	    } //Update

        public bool Delete(String tableName, String where)
	    {
	        Boolean returnCode = true;
	        try
	        {
	            this.ExecuteNonQuery(String.Format("delete from {0} where {1};", tableName, where));
	        }
	        catch (Exception fail)
	        {
	            MessageBox.Show(fail.Message);
	            returnCode = false;
	        }
	        return returnCode;
	    } //Delete

        public bool Insert(String tableName, Dictionary<String, String> data)
	    {
	        String columns = "";
	        String values = "";
	        Boolean returnCode = true;
	        foreach (KeyValuePair<String, String> val in data)
	        {
	            columns += String.Format(" {0},", val.Key.ToString());
	            values += String.Format(" '{0}',", val.Value);
	        }
	        columns = columns.Substring(0, columns.Length - 1);
	        values = values.Substring(0, values.Length - 1);
	        try
	        {
	            this.ExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", tableName, columns, values));
	        }
	        catch(Exception fail)
	        {
	            MessageBox.Show(fail.Message);
	            returnCode = false;
	        }
	        return returnCode;
        } //Insert

        public bool ClearDB()
	    {
	        DataTable tables;
	        try
	        {
	            tables = this.GetDataTable("select NAME from SQLITE_MASTER where type='table' order by NAME;");
	            foreach (DataRow table in tables.Rows)
	            {
	                this.ClearTable(table["NAME"].ToString());
	            }
	            return true;
	        }
	        catch
	        {
	            return false;
	        }
	    } //ClearDB

        public bool ClearTable(String table)
	    {
	        try
	        {	             
	            this.ExecuteNonQuery(String.Format("delete from {0};", table));
	            return true;
	        } 
	        catch
	        {
	            return false;
	        }
        } //ClearTable    

    }
}
